package cn.gson.boot.model.dao;

import cn.gson.boot.model.pojos.Hetong;
import cn.gson.boot.model.pojos.Quotation;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import javax.transaction.Transactional;
import java.util.List;

public interface HeTongDao extends CrudRepository<Hetong,Integer> {
    //根据合同id来移除合同 （逻辑删除）
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "update hetong set ht_ys=0 where ht_id=?1")
    public void yichuHeTong(Integer htid);
    //根据合同id来删除合同 （物理删除）
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "delete from hetong where ht_id=?1")
    public void delectHeTong(Integer htid);


    //新建订单，查看所有的合同状态是执行中的合同
    @Query(nativeQuery = true,value = "select * from hetong where ht_zhuangtai = '同意' and ht_ys = 1 order by ht_id desc")
    public List<Hetong> ddCkHt();

    //根据合同号查到合同id
    @Query(nativeQuery = true,value = "select ht_id from hetong where ht_hao = ?")
    public Integer selectDDHtIdByHtHao(String htHao);

 /*   //查看所有合同
    @Query(nativeQuery = true,value = "select * from hetong")
    public List<Hetong> selectHeTongOne();

    //根据合同号模糊查询合同
    @Query(nativeQuery = true,value = "select * from hetong where ht_hao like ?1")
    public List<Hetong> selectHeTongTwo(String htHao);*/

    //已审核(同意)的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_sh='同意' and ht_ys=1")
    public Integer yshHt();
    //待审核的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_sh='待审核' and ht_ys=1")
    public Integer dshHt();
    //审核中的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_sh='审核中' and ht_ys=1")
    public Integer shzHt();
    //否决的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_sh='否决' and ht_ys=1")
    public Integer fjHt();

    //未开始
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_zhuangtai='未开始' and ht_ys=1")
    public Integer wksHt();
    //完成的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_zhuangtai='完成' and ht_ys=1")
    public Integer wcHt();
    //执行中的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_zhuangtai='执行中' and ht_ys=1")
    public Integer zxzHt();
    //意外终止的合同
    @Query(nativeQuery = true,value = "select count(*) from hetong where ht_zhuangtai='意外终止' and ht_ys=1")
    public Integer ywzzHt();

    //用户
    @Query(nativeQuery = true,value = "select count(*) from hetong where k_id=?1 and ht_ys=1")
    public Integer khHt(Integer kId);

    //报价单
    @Query(nativeQuery = true,value = "select count(*) from hetong where Quotation_id=?1 and ht_ys=1")
    public Integer bjdHt(Integer bjd);

    //所有者
    @Query(nativeQuery = true,value = "select count(*) from hetong where emp_id=?1 and ht_ys=1")
    public Integer syzHt(Integer syz);



    //审核
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "update hetong set ht_sh=?1 where ht_id=?2")
    public void shenheHeTong(String sh,Integer htid);



    //谭龙俊的客户合并（合同表）
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "update hetong set k_id = ?1 where k_id=?2")
    public void kehuhebing(Integer b,Integer a);


    //谭龙俊的客户合并（合同表）
    @Query(nativeQuery = true,value = "select * from hetong where  k_id = ?1")
    public List<Hetong> hetongcha(Integer a);
}
